package com.yoyo.itravel.dao;

import com.yoyo.itravel.bean.Sight;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.stereotype.Repository;

import javax.servlet.jsp.jstl.sql.SQLExecutionTag;
import java.sql.SQLException;
import java.util.List;

/**
 * @Description 景点操作持久层接口
 * @Author HZX
 * @Version 1.0
 * @Date 2020/6/26 11:29
 */
@Repository
public interface ISightDao {

    /**
     * @Description 插入景点
     *
     * @Param sight
     * @Return int
     */
    @Insert("insert into tb_sight(sightId, sightName, openTime, introduce, orderNotice, isOnSell, collectCount," +
            " countryId, provinceId, cityId, detailAddr, sightImg, sellCount, sightCompanyId) " +
            " values(#{sightId},#{sightName},#{openTime},#{introduce},#{orderNotice},#{isOnSell},#{collectCount}," +
            " #{countryId},#{provinceId},#{cityId},#{detailAddr},#{sightImg},#{sellCount},#{sightCompanyId})")
    int insert(Sight sight) throws SQLException;

    /**
     * @Description 通过景点id删除景点
     *
     * @Param sightId
     * @Return int
     */
    @Delete("delete from tb_sight where sightId=#{sightId}")
    int deleteById(String sightId) throws SQLException;

    /**
     * @Description 通过景点id查询景点
     *
     * @Param sightId
     * @Return int
     */
    @Select("select * from tb_sight where sightId=#{sightId}")
    Sight selectById(String sightId) throws SQLException;

    /**
     * @Description 根据条件查询景点（按照偏移量）（XML方式实现）
     *
     * @Param themeId
     * @Param provinceId
     * @Param cityId
     * @Param start
     * @Param pageSize
     * @Param search
     * @Return java.util.List<com.yoyo.itravel.bean.Sight>
     */
    List<Sight> selectPageByCondition(@Param("themeId") String themeId, @Param("provinceId") String provinceId, @Param("cityId") String cityId, @Param("start") int start, @Param("pageSize") int pageSize, @Param("search") String search) throws SQLException;

    /**
     * @Description 根据条件查询景点（XML方式实现）
     *
     * @param themeId
     * @param provinceId
     * @param cityId
     * @param search
     * @return java.util.List<cn.hut.travel.bean.Sight>
     */
    List<Sight> selectByCondition(@Param("themeId") String themeId, @Param("provinceId") String provinceId, @Param("cityId") String cityId, @Param("search") String search) throws SQLException;

    /**
     * @Description 根据条件获得查询的景点数据条数（XML方式实现）
     *
     * @param themeId
     * @param provinceId
     * @param cityId
     * @param search
     * @return int
     */
    Integer selectCountByCondition(@Param("themeId") String themeId, @Param("provinceId") String provinceId, @Param("cityId") String cityId, @Param("search") String search) throws SQLException;

    /**
     * @Description 通过景点id查询景点
     *
     * @param sightId
     * @return cn.hut.travel.bean.Sight
     */
    @Select("select * from tb_sight where sightId=#{sightId}")
    Sight selectBySightId(String sightId) throws SQLException;

    /**
     * @Description 通过景点id更新景点门票销量
     *
     * @param sightId
     * @param count
     * @return int
     */
    @Update("update tb_sight set sellCount=#{count} where sightId=#{sightId}")
    int updateSellCount(@Param("sightId") String sightId, @Param("count") Integer count) throws SQLException;

    /**
     * @Description 通过景点id更新景点被收藏的次数
     *
     * @param sightId
     * @param isAdd  true:加1   false:减1
     * @return int
     */

    @Update({"<script> " +
            "   update tb_sight set " +
            "   <when test='isAdd'> collectCount=collectCount+1 </when>   " +
            "   <when test='!isAdd'> collectCount=collectCount-1 </when>  " +
            "   where sightId=#{sightId} " +
            "</script>"})
    int updateCollectCount(@Param("sightId") String sightId, @Param("isAdd") boolean isAdd) throws SQLException;

    /**
     * @Description 查找景点--按销量逆序排序
     *
     * @param size
     * @return cn.hut.travel.bean.PageBean<cn.hut.travel.bean.Sight>
     */
    @Select("select * from tb_sight order by sellCount desc limit 0, #{size}")
    List<Sight> selectSellestSight(Integer size) throws SQLException;

    /**
     * @Description 分页查询所有景点
     *
     * @param start
     * @param pageSize
     * @return java.util.List<cn.hut.travel.bean.Sight>
     */
    @Select("select * from tb_sight limit #{start}, #{pageSize}")
    List<Sight> selectAllByPage(@Param("start") int start, @Param("pageSize") int pageSize) throws SQLException;

    /**
     * @Description 查询记录的总条数
     *
     * @param
     * @return int
     */
    @Select("select count(*) from tb_sight")
    int selectTotalCount() throws SQLException;

    /**
     * @Description 查找景点--按收藏量逆序排序
     *
     * @param size
     * @return java.util.List<cn.hut.travel.bean.Sight>
     */
    @Select("select * from tb_sight order by collectCount desc limit 0, #{size}")
    List<Sight> selectCollectestSight(Integer size) throws SQLException;

    /**
     * @Description 通过用户id查询该用户收藏的所有景点
     *
     * @param userId
     * @return java.util.List<cn.hut.travel.bean.Sight>
     */
    @Select("select s.* from middle_tb_sight_collect c join tb_sight s on c.sightId = s.sightId and c.userId=#{userId}")
    List<Sight> selectAllCollectByUserId(String userId) throws SQLException;

    /**
     * @Description 通过景点id修改景点的在售状态
     *
     * @Param sightId
     * @Param newStatus
     * @Return int
     */
    @Update("update tb_sight set isOnSell=#{newStatus} where sightId=#{sightId}")
    int updateOnSellStatusById(@Param("sightId") String sightId, @Param("newStatus") Integer newStatus) throws SQLException;


    Integer deleteBatch(String[] ids) throws SQLException;
}
